﻿Imports System.Data.Linq
Imports System.Linq

Public Class MPaymentVoucher
    Private db As New TanNamChinhDataContext
#Region "Thanh's codes"
    'Thanh thêm vào ngày 23/09/2013
    'Hàm lấy thông tin PaymentVoucher theo ID
    Public Function SelectRecordById(ByVal id As Integer) As tblPaymentVoucher
        Try
            Return (From pv In db.tblPaymentVouchers
                    Where pv.ID_PaymentVoucher = id
                    Select pv).Single()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
#End Region
    Public Function GetVoucherList() As IQueryable
        Dim query
        Try
            query = From b In db.tblPaymentVouchers
             Order By b.PaymentVoucherNo Descending
            Select
             b.ID_PaymentVoucher,
             b.PaymentVoucherNo,
                b.CreatedDate,
                b.Reciever,
                b.Amount,
                b.ID_Director,
                Director = If(b.tblUser IsNot Nothing, b.tblUser.FullName, String.Empty),
                b.Cashier,
                b.ID_User,
                Nguoilapphieu = If(b.tblUser1 IsNot Nothing, b.tblUser1.FullName, String.Empty),
                b.Description,
                b.ID_ApproveUser,
                ApproveUser = If(b.tblUser2 IsNot Nothing, b.tblUser2.FullName, String.Empty),
                b.IsApprove

            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetVoucherListSearch(ByVal idFile As Integer) As IQueryable
        Dim query
        Dim q = (From b In db.tblFileFees
                Where b.ID_File = idFile
                Select b.ID_PaymentVoucher).Distinct
        Try
            query = From b In db.tblPaymentVouchers
                    Where q.Contains(b.ID_PaymentVoucher)
             Order By b.PaymentVoucherNo Descending
            Select
             b.ID_PaymentVoucher,
             b.PaymentVoucherNo,
                b.CreatedDate,
                b.Reciever,
                b.Amount,
                b.ID_Director,
                Director = If(b.tblUser IsNot Nothing, b.tblUser.FullName, String.Empty),
                b.Cashier,
                b.ID_User,
                Nguoilapphieu = If(b.tblUser1 IsNot Nothing, b.tblUser1.FullName, String.Empty),
                b.Description,
                b.ID_ApproveUser,
                ApproveUser = If(b.tblUser2 IsNot Nothing, b.tblUser2.FullName, String.Empty),
                b.IsApprove

            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetVoucherList(ByVal dateAdvance As DateTime) As IQueryable
        Dim query
        Try
            query = From b In db.tblPaymentVouchers
                    Where b.CreatedDate.Value = dateAdvance.ToShortDateString
             Order By b.CreatedDate Descending
            Select
             b.ID_PaymentVoucher,
             b.PaymentVoucherNo,
                b.CreatedDate,
                b.Reciever,
                b.Amount,
                b.ID_Director,
                Director = If(b.tblUser IsNot Nothing, b.tblUser.FullName, String.Empty),
                b.Cashier,
                b.ID_User,
                Nguoilapphieu = If(b.tblUser1 IsNot Nothing, b.tblUser1.FullName, String.Empty),
                b.Description
            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
  
    Public Function GetListFee(ByVal idFile As Integer) As IQueryable
        Dim query
        Try
            query = From b In db.tblFileFees
                    Where b.ID_File = idFile And b.tblFee.ID_FeeType = 1 And Not b.ID_AdvanceVoucher.HasValue And b.tblFee.IsActive = True
            Select
            New With {
            b.ID_FileFee,
           b.ID_File,
           b.ID_Fee,
           .FeeName = If(b.tblFee IsNot Nothing, b.tblFee.FeeName, String.Empty),
           b.Price,
           b.ID_Currency,
           .Curr = If(b.tblCurrency IsNot Nothing, b.tblCurrency.Code, String.Empty),
           .Ratio = 1,
           b.Description}
            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetListFee2(ByVal idFile As Integer) As IQueryable
        Dim query1
        Dim query2
        Try
            query1 = From b In db.tblFileFees Join a In db.tblFileExchanges On a.ID_File Equals b.ID_File And a.ID_Currency Equals b.ID_Currency
                    Where b.ID_File = idFile And b.tblFee.ID_FeeType = 1 And Not b.ID_AdvanceVoucher.HasValue And b.UnitPrice > 0
            Order By b.tblFee.Rank
             Select
             b.ID_FileFee,
             b.ID_File,
             b.ID_Fee,
             FeeName = If(b.tblFee IsNot Nothing, b.tblFee.FeeName, String.Empty),
             b.Price,
             b.ID_Currency,
             Curr = If(b.tblCurrency IsNot Nothing, b.tblCurrency.Code, String.Empty),
             a.Ratio,
             b.Description
            Return query1
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Sub InsertNewRecord(ByVal obj As tblPaymentVoucher)
        'db.tblOilPayments.InsertOnSubmit(obj)
        db.GetTable(Of tblPaymentVoucher)().InsertOnSubmit(obj)
        db.SubmitChanges()
    End Sub
    Public Sub UpdateRecord(ByVal obj As tblPaymentVoucher)
        Dim getData = (From b In db.tblPaymentVouchers
                                Where b.ID_PaymentVoucher = obj.ID_PaymentVoucher
                                Select b).Single()
        '  getData.ID_AdvanceVoucher = obj.ID_AdvanceVoucher
        getData.ID_User = obj.ID_User
        getData.CreatedDate = obj.CreatedDate
        getData.PaymentVoucherNo = obj.PaymentVoucherNo
        getData.Description = obj.Description
        getData.Reciever = obj.Reciever
        getData.Amount = obj.Amount
        getData.ID_Director = obj.ID_Director
        getData.Cashier = obj.Cashier
        getData.ID_User = obj.ID_User
        '   getData.InvoiceNo = obj.InvoiceNo

        db.SubmitChanges()
        'End If



    End Sub
    Public Sub DeleteRecord(ByVal id As Integer)
        Dim getData = (From b In db.tblPaymentVouchers
                                Where b.ID_PaymentVoucher = id
                                Select b).Single()
        db.tblPaymentVouchers.DeleteOnSubmit(getData)
        db.SubmitChanges()
    End Sub
    ''' <summary>
    ''' Kiểm tra File này đã lập phiếu chi chưa. Nếu có trả về True
    ''' </summary>
    ''' <param name="idFile"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExistPaymentVoucher(ByVal idFile As Integer) As Boolean
        Try
            Dim isE = (From b In db.tblFileFees
                 Where b.ID_File = idFile
                 Select b.ID_PaymentVoucher).First()

            If isE IsNot Nothing Then
                'Nếu có tồn tại
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Return False
        End Try
       


    End Function
    'Kiểm tra phiếu tạm ứng nào chưa nộp hoá đơn sau 2 ngày
    Public Function CheckSlowInvoice(ByVal days As Integer)
        Dim query
        Dim oldestDate As DateTime = DateTime.Now.Subtract(New TimeSpan(2, 0, 0, 0, 0))
        Try
            query = From b In db.tblAdvanceVouchers
                Where b.CreatedDate > oldestDate
            Select b


            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function getIDNewPaymentVoucher() As Integer
        Dim id_pv As Integer
        Try
            id_pv = (From b In db.tblPaymentVouchers
                     Order By b.ID_PaymentVoucher Descending
                        Select b.ID_PaymentVoucher
                       ).First()
        Catch ex As Exception
            Throw ex
        End Try
        Return id_pv
    End Function
    Public Function getIDPaymentVoucherOfFile(ByVal idFile As Integer) As Integer
        Dim id_pv As Integer
        Try
            id_pv = (From b In db.tblFileFees
                    Where b.ID_File = idFile
                        Select b.ID_PaymentVoucher
                       ).First()
        Catch ex As Exception
            'Nếu có lỗi trả về tức là null
            Return 0
        End Try
        Return id_pv
    End Function
    Public Function getFileOfPaymentVoucher(ByVal idPaymentV As Integer) As Integer
        Dim id_f As Integer
        Try
            id_f = (From b In db.tblFileFees
                     Where b.ID_PaymentVoucher = idPaymentV
                    Select b.ID_File
                       ).First()
        Catch ex As Exception
            Throw ex
        End Try
        Return id_f
    End Function
    Public Function GetListDetailPaymentSlow(ByVal days As Integer) As IQueryable
        'Lấy ra thông tin Payment nào cách hiện tại số ngày nhất định, mà trong danh sách 
        'file fee chưa thấy có id_reciept , tức là chưa có phiếu thu tương ứng
        Dim oldestDate As DateTime = DateTime.Now.Subtract(New TimeSpan(days, 0, 0, 0, 0))
        'Danh sách các phiếu chi cho bên hãng tàu (chậm)
        Dim listPm = From a In db.tblFileFees
                     Where a.tblPaymentVoucher.CreatedDate < oldestDate
                     Select a.ID_File

        If listPm.Count <= 0 Then
            'nếu mà không có file nào chậm
            Return Nothing
        End If

        'Từ danh sách các paymentvoucher chậm này, xem thử trong file fee đã thu tiền từ khách hàng hay chưa

        ' danh sách file đã được thanh toán cho bên hãng tàu
        Dim q1 = From a In db.tblFileFees
                 Where listPm.Contains(a.ID_PaymentVoucher)
                 Select a.ID_File
        'Trả về danh sách các file đã thanh toán bên hãng tàu nhưng khách hàng chưa thanh toán tiền ', So_ngay_cham = DateTime.Now().Subtract(a.tblPaymentVoucher.CreatedDate).Days
        'Dim query = From a In db.tblSellingFees
        '                 Where q1.Contains(a.tblHBL.tblFileMBL.ID_File) And Not a.ID_ReceiptVoucher.HasValue
        '                 Select a.tblHBL.tblFileMBL.tblFile.FileNo, a.tblHBL.tblFileMBL.tblFile.CreatedDate,
        '                  CustomerName = If(a.tblHBL.tblFileMBL.tblFile.tblCustomer IsNot Nothing, a.tblHBL.tblFileMBL.tblFile.tblCustomer.CustomerName, String.Empty),
        '                Company = If(a.tblHBL.tblFileMBL.tblFile.tblCompany IsNot Nothing, a.tblHBL.tblFileMBL.tblFile.tblCompany.Name, String.Empty)

        Dim query = (From a In db.tblSellingFees
                      Where listPm.Contains(a.tblHBL.tblFileMBL.ID_File) And Not a.ID_ReceiptVoucher.HasValue
                      Select a.tblHBL.tblFileMBL.tblFile.FileNo, a.tblHBL.tblFileMBL.tblFile.CreatedDate,
                       CustomerName = If(a.tblHBL.tblFileMBL.tblFile.tblCustomer IsNot Nothing, a.tblHBL.tblFileMBL.tblFile.tblCustomer.CustomerName, String.Empty),
                     Company = If(a.tblHBL.tblFileMBL.tblFile.tblCompany IsNot Nothing, a.tblHBL.tblFileMBL.tblFile.tblCompany.Name, String.Empty)
        ).Distinct()
        Return query
    End Function
    Public Function GetListFileOrContOfAdvanceSlow(ByVal idAdvance As Integer) As IQueryable
        Dim query
        'Nếu là file


        'Trả về danh sách file
        query = (From a In db.tblFileFees
                 Where a.ID_AdvanceVoucher = idAdvance
                 Select a.tblFile.FileNo, a.tblFile.CreatedDate,
                  CustomerName = If(a.tblFile.tblCustomer IsNot Nothing, a.tblFile.tblCustomer.CustomerName, String.Empty),
                Company = If(a.tblFile.tblCompany IsNot Nothing, a.tblFile.tblCompany.Name, String.Empty)
).Distinct()

        Return query
    End Function
    Public Function GetLastedPaymentNo() As Integer
        Try
            Dim query As String = (From a In db.tblPaymentVouchers
                               Order By a.ID_PaymentVoucher Descending
                               Select a.PaymentVoucherNo).First()


            Dim str() = query.Split("/")
            Return Integer.Parse(str(0))

        Catch ex As Exception
            Return 0
        End Try

    End Function
End Class
